﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public static class LocationDB
    {
        public static List<Location> ShowAll()
        {
            List<Location> myLocations = new List<Location>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * From Location";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Location location = new Location();
                    location.Id = (int)reader["Id"];
                    location.Name = reader["Name"].ToString();
                    myLocations.Add(location);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return myLocations;
        }

        public static int GetLocationIdByName(string LocationName)
        {
            int LocationId = 0;
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT Id From Location WHERE Name = @Location_Name;";
            cmd.Parameters.AddWithValue("@Location_Name", LocationName);
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    LocationId = (int)reader["Id"];
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return LocationId;
        }
    }
}
